{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "ce5ec919-7c70-4a8b-820d-6ce08abe531a",
   "metadata": {},
   "source": [
    "Run these cells first to install the libraries necessary to complete the tutorial."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "989e388c-5671-40ab-a0e0-4c7d9b61d664",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: pandas in c:\\users\\ian\\pivot_tables\\venv\\lib\\site-packages (2.2.1)\n",
      "Requirement already satisfied: pyarrow in c:\\users\\ian\\pivot_tables\\venv\\lib\\site-packages (15.0.0)\n",
      "Requirement already satisfied: numpy<2,>=1.26.0 in c:\\users\\ian\\pivot_tables\\venv\\lib\\site-packages (from pandas) (1.26.4)\n",
      "Requirement already satisfied: python-dateutil>=2.8.2 in c:\\users\\ian\\pivot_tables\\venv\\lib\\site-packages (from pandas) (2.9.0.post0)\n",
      "Requirement already satisfied: pytz>=2020.1 in c:\\users\\ian\\pivot_tables\\venv\\lib\\site-packages (from pandas) (2024.1)\n",
      "Requirement already satisfied: tzdata>=2022.7 in c:\\users\\ian\\pivot_tables\\venv\\lib\\site-packages (from pandas) (2024.1)\n",
      "Requirement already satisfied: six>=1.5 in c:\\users\\ian\\pivot_tables\\venv\\lib\\site-packages (from python-dateutil>=2.8.2->pandas) (1.16.0)\n"
     ]
    }
   ],
   "source": [
    "!pip install pandas pyarrow"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fdc3105d-3d74-48ac-80cd-4c3c5c45e1d6",
   "metadata": {},
   "source": [
    "Run this code to import your dataset from `sales_data.csv`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "2e0c9ffa-5cab-4cef-bca4-c10bcb687bc8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_number</th>\n",
       "      <th>employee_id</th>\n",
       "      <th>employee_name</th>\n",
       "      <th>job_title</th>\n",
       "      <th>sales_region</th>\n",
       "      <th>order_date</th>\n",
       "      <th>order_type</th>\n",
       "      <th>customer_type</th>\n",
       "      <th>customer_name</th>\n",
       "      <th>customer_state</th>\n",
       "      <th>product_category</th>\n",
       "      <th>product_number</th>\n",
       "      <th>produce_name</th>\n",
       "      <th>quantity</th>\n",
       "      <th>unit_price</th>\n",
       "      <th>sale_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1102935</td>\n",
       "      <td>900019019</td>\n",
       "      <td>Alexandra Kundt</td>\n",
       "      <td>Senior Sales Associate</td>\n",
       "      <td>S Central East</td>\n",
       "      <td>2019-02-09 00:00:00</td>\n",
       "      <td>Retail</td>\n",
       "      <td>Individual</td>\n",
       "      <td>Skipton Fealty</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>Olive Oil</td>\n",
       "      <td>OO206</td>\n",
       "      <td>Chili Extra Virgin Olive Oil 2pk</td>\n",
       "      <td>3</td>\n",
       "      <td>45.0</td>\n",
       "      <td>135.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1102976</td>\n",
       "      <td>900019019</td>\n",
       "      <td>Alexandra Kundt</td>\n",
       "      <td>Senior Sales Associate</td>\n",
       "      <td>S Central East</td>\n",
       "      <td>2019-02-15 00:00:00</td>\n",
       "      <td>Retail</td>\n",
       "      <td>Individual</td>\n",
       "      <td>Lanni D'Ambrogi</td>\n",
       "      <td>Missouri</td>\n",
       "      <td>Gift Basket</td>\n",
       "      <td>GB301</td>\n",
       "      <td>Scented Olive Oil Candle Gift Basket</td>\n",
       "      <td>1</td>\n",
       "      <td>19.5</td>\n",
       "      <td>19.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1102988</td>\n",
       "      <td>900019019</td>\n",
       "      <td>Alexandra Kundt</td>\n",
       "      <td>Senior Sales Associate</td>\n",
       "      <td>S Central East</td>\n",
       "      <td>2019-02-16 00:00:00</td>\n",
       "      <td>Retail</td>\n",
       "      <td>Individual</td>\n",
       "      <td>Far Pow</td>\n",
       "      <td>Mississippi</td>\n",
       "      <td>Olive Oil</td>\n",
       "      <td>OO302</td>\n",
       "      <td>Chili Extra Virgin Olive Oil</td>\n",
       "      <td>4</td>\n",
       "      <td>26.0</td>\n",
       "      <td>104.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1103012</td>\n",
       "      <td>900019019</td>\n",
       "      <td>Alexandra Kundt</td>\n",
       "      <td>Senior Sales Associate</td>\n",
       "      <td>S Central East</td>\n",
       "      <td>2019-02-19 00:00:00</td>\n",
       "      <td>Wholesale</td>\n",
       "      <td>Business</td>\n",
       "      <td>Swift Inc</td>\n",
       "      <td>Texas</td>\n",
       "      <td>Olive Oil</td>\n",
       "      <td>OO125</td>\n",
       "      <td>Garlic Extra Virgin Olive Oil 12pk</td>\n",
       "      <td>4</td>\n",
       "      <td>234.0</td>\n",
       "      <td>936.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1103031</td>\n",
       "      <td>900019019</td>\n",
       "      <td>Alexandra Kundt</td>\n",
       "      <td>Senior Sales Associate</td>\n",
       "      <td>S Central East</td>\n",
       "      <td>2019-02-22 00:00:00</td>\n",
       "      <td>Retail</td>\n",
       "      <td>Individual</td>\n",
       "      <td>Carmine Priestnall</td>\n",
       "      <td>Texas</td>\n",
       "      <td>Olive Oil</td>\n",
       "      <td>OO128</td>\n",
       "      <td>Chili Extra Virgin Olive Oil 12pk</td>\n",
       "      <td>3</td>\n",
       "      <td>234.0</td>\n",
       "      <td>702.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   order_number  employee_id    employee_name               job_title  \\\n",
       "0       1102935    900019019  Alexandra Kundt  Senior Sales Associate   \n",
       "1       1102976    900019019  Alexandra Kundt  Senior Sales Associate   \n",
       "2       1102988    900019019  Alexandra Kundt  Senior Sales Associate   \n",
       "3       1103012    900019019  Alexandra Kundt  Senior Sales Associate   \n",
       "4       1103031    900019019  Alexandra Kundt  Senior Sales Associate   \n",
       "\n",
       "     sales_region           order_date order_type customer_type  \\\n",
       "0  S Central East  2019-02-09 00:00:00     Retail    Individual   \n",
       "1  S Central East  2019-02-15 00:00:00     Retail    Individual   \n",
       "2  S Central East  2019-02-16 00:00:00     Retail    Individual   \n",
       "3  S Central East  2019-02-19 00:00:00  Wholesale      Business   \n",
       "4  S Central East  2019-02-22 00:00:00     Retail    Individual   \n",
       "\n",
       "        customer_name customer_state product_category product_number  \\\n",
       "0      Skipton Fealty       Arkansas        Olive Oil          OO206   \n",
       "1     Lanni D'Ambrogi       Missouri      Gift Basket          GB301   \n",
       "2             Far Pow    Mississippi        Olive Oil          OO302   \n",
       "3           Swift Inc          Texas        Olive Oil          OO125   \n",
       "4  Carmine Priestnall          Texas        Olive Oil          OO128   \n",
       "\n",
       "                           produce_name  quantity  unit_price  sale_price  \n",
       "0      Chili Extra Virgin Olive Oil 2pk         3        45.0       135.0  \n",
       "1  Scented Olive Oil Candle Gift Basket         1        19.5        19.5  \n",
       "2          Chili Extra Virgin Olive Oil         4        26.0       104.0  \n",
       "3    Garlic Extra Virgin Olive Oil 12pk         4       234.0       936.0  \n",
       "4     Chili Extra Virgin Olive Oil 12pk         3       234.0       702.0  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "sales_data = pd.read_csv(\n",
    "    filepath_or_buffer=\"sales_data.csv\",\n",
    "    parse_dates=[\"order_date\"],\n",
    "    dayfirst=True,\n",
    ").convert_dtypes(dtype_backend=\"pyarrow\")\n",
    "\n",
    "sales_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "87feff16-0105-40e9-b981-70057fc75ac8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "order_number                int64[pyarrow]\n",
       "employee_id                 int64[pyarrow]\n",
       "employee_name              string[pyarrow]\n",
       "job_title                  string[pyarrow]\n",
       "sales_region               string[pyarrow]\n",
       "order_date          timestamp[ns][pyarrow]\n",
       "order_type                 string[pyarrow]\n",
       "customer_type              string[pyarrow]\n",
       "customer_name              string[pyarrow]\n",
       "customer_state             string[pyarrow]\n",
       "product_category           string[pyarrow]\n",
       "product_number             string[pyarrow]\n",
       "produce_name               string[pyarrow]\n",
       "quantity                    int64[pyarrow]\n",
       "unit_price                 double[pyarrow]\n",
       "sale_price                 double[pyarrow]\n",
       "dtype: object"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sales_data.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4ff4f07e-afde-4fd3-9eff-ee106d3b8995",
   "metadata": {},
   "source": [
    "**Possible Solution: Planning and Creating a Pivot Table**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "7e19d581-3c9c-42d4-a704-4563b318c6df",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>sales_region</th>\n",
       "      <th>Central East</th>\n",
       "      <th>N Central East</th>\n",
       "      <th>N Central West</th>\n",
       "      <th>Northeast</th>\n",
       "      <th>Northwest</th>\n",
       "      <th>S Central East</th>\n",
       "      <th>S Central West</th>\n",
       "      <th>Southeast</th>\n",
       "      <th>Southwest</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>product_category</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Bath products</th>\n",
       "      <td>$120.00</td>\n",
       "      <td>$174.00</td>\n",
       "      <td>$93.50</td>\n",
       "      <td>$145.00</td>\n",
       "      <td>$96.85</td>\n",
       "      <td>$300.00</td>\n",
       "      <td>$90.00</td>\n",
       "      <td>$270.00</td>\n",
       "      <td>$174.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Gift Basket</th>\n",
       "      <td>$900.00</td>\n",
       "      <td>$1_050.00</td>\n",
       "      <td>$460.00</td>\n",
       "      <td>$690.00</td>\n",
       "      <td>$900.00</td>\n",
       "      <td>$920.00</td>\n",
       "      <td>$825.00</td>\n",
       "      <td>$1_150.00</td>\n",
       "      <td>$900.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Olive Oil</th>\n",
       "      <td>$3_276.00</td>\n",
       "      <td>$3_276.00</td>\n",
       "      <td>$936.00</td>\n",
       "      <td>$3_276.00</td>\n",
       "      <td>$2_808.00</td>\n",
       "      <td>$3_276.00</td>\n",
       "      <td>$3_276.00</td>\n",
       "      <td>$3_276.00</td>\n",
       "      <td>$3_276.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "sales_region      Central East  N Central East  N Central West  Northeast  \\\n",
       "product_category                                                            \n",
       "Bath products          $120.00         $174.00          $93.50    $145.00   \n",
       "Gift Basket            $900.00       $1_050.00         $460.00    $690.00   \n",
       "Olive Oil            $3_276.00       $3_276.00         $936.00  $3_276.00   \n",
       "\n",
       "sales_region      Northwest  S Central East  S Central West  Southeast  \\\n",
       "product_category                                                         \n",
       "Bath products        $96.85         $300.00          $90.00    $270.00   \n",
       "Gift Basket         $900.00         $920.00         $825.00  $1_150.00   \n",
       "Olive Oil         $2_808.00       $3_276.00       $3_276.00  $3_276.00   \n",
       "\n",
       "sales_region      Southwest  \n",
       "product_category             \n",
       "Bath products       $174.00  \n",
       "Gift Basket         $900.00  \n",
       "Olive Oil         $3_276.00  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.set_option(\"display.float_format\", \"${:_.2f}\".format)\n",
    "\n",
    "sales_data.pivot_table(\n",
    "    values=\"sale_price\",\n",
    "    index=\"product_category\",\n",
    "    columns=\"sales_region\",\n",
    "    aggfunc=\"max\",\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c9a9df7d-5d01-49af-a15c-d29169072ffb",
   "metadata": {},
   "source": [
    "As you learned earlier, the first step is to plan out what you want to see. The specification asks you to work out maximum values so setting `aggfunc=\"max\"` will be necessary. To separate out the different sales regions into their own columns, you assign `\"sales_region\"` to your `columns` parameter. \n",
    "\n",
    "To separate out each of the different product categories into their own rows, you set `index` to `\"product_category\"`. To make sure the calculations are based on sales prices, you assign `\"sale_price\"` to the `values` parameter. To format the data you use a similar format string with `$` and for the comma-separator, you replace comma (`,`) with an underscore `(_)`."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a8a86f2d-0b09-4aff-90e1-6bc5254e3d09",
   "metadata": {},
   "source": [
    "**Possible Solution: Using Sub-Columns**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "be518313-1e98-45cc-99a3-ef99186f6a82",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th>customer_type</th>\n",
       "      <th colspan=\"3\" halign=\"left\">Business</th>\n",
       "      <th colspan=\"3\" halign=\"left\">Individual</th>\n",
       "      <th>Max Quantity</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>product_category</th>\n",
       "      <th>Bath products</th>\n",
       "      <th>Gift Basket</th>\n",
       "      <th>Olive Oil</th>\n",
       "      <th>Bath products</th>\n",
       "      <th>Gift Basket</th>\n",
       "      <th>Olive Oil</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>customer_state</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Alabama</th>\n",
       "      <td>14</td>\n",
       "      <td>12</td>\n",
       "      <td>11</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Alaska</th>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Arizona</th>\n",
       "      <td>4</td>\n",
       "      <td>11</td>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Arkansas</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>California</th>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>14</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Colorado</th>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Connecticut</th>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>14</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Delaware</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>District of Columbia</th>\n",
       "      <td>14</td>\n",
       "      <td>0</td>\n",
       "      <td>14</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Florida</th>\n",
       "      <td>14</td>\n",
       "      <td>9</td>\n",
       "      <td>14</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Georgia</th>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>14</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hawaii</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Idaho</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Illinois</th>\n",
       "      <td>14</td>\n",
       "      <td>6</td>\n",
       "      <td>13</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Indiana</th>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Iowa</th>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>14</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kansas</th>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kentucky</th>\n",
       "      <td>3</td>\n",
       "      <td>12</td>\n",
       "      <td>13</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Louisiana</th>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Maryland</th>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>14</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Massachusetts</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>14</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Michigan</th>\n",
       "      <td>2</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Minnesota</th>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mississippi</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Missouri</th>\n",
       "      <td>10</td>\n",
       "      <td>5</td>\n",
       "      <td>14</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Montana</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Nebraska</th>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Nevada</th>\n",
       "      <td>12</td>\n",
       "      <td>11</td>\n",
       "      <td>14</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>New Hampshire</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>New Jersey</th>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>14</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>New Mexico</th>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>New York</th>\n",
       "      <td>10</td>\n",
       "      <td>3</td>\n",
       "      <td>14</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>North Carolina</th>\n",
       "      <td>10</td>\n",
       "      <td>7</td>\n",
       "      <td>13</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>North Dakota</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ohio</th>\n",
       "      <td>12</td>\n",
       "      <td>13</td>\n",
       "      <td>14</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Oklahoma</th>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Oregon</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>14</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pennsylvania</th>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>South Carolina</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>South Dakota</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Tennessee</th>\n",
       "      <td>14</td>\n",
       "      <td>0</td>\n",
       "      <td>14</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Texas</th>\n",
       "      <td>12</td>\n",
       "      <td>13</td>\n",
       "      <td>14</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Utah</th>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>14</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Vermont</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>14</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Virginia</th>\n",
       "      <td>14</td>\n",
       "      <td>13</td>\n",
       "      <td>14</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Washington</th>\n",
       "      <td>13</td>\n",
       "      <td>12</td>\n",
       "      <td>14</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>West Virginia</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>14</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wisconsin</th>\n",
       "      <td>7</td>\n",
       "      <td>13</td>\n",
       "      <td>13</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wyoming</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Max Quantity</th>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "customer_type             Business                          Individual  \\\n",
       "product_category     Bath products Gift Basket Olive Oil Bath products   \n",
       "customer_state                                                           \n",
       "Alabama                         14          12        11             4   \n",
       "Alaska                           7           0         8             4   \n",
       "Arizona                          4          11        12             4   \n",
       "Arkansas                         0           0        13             4   \n",
       "California                      12          12        14             4   \n",
       "Colorado                         8           2        10             4   \n",
       "Connecticut                      1           6        14             4   \n",
       "Delaware                         2           2         7             4   \n",
       "District of Columbia            14           0        14             4   \n",
       "Florida                         14           9        14             4   \n",
       "Georgia                         12          10        14             4   \n",
       "Hawaii                           0           0         0             4   \n",
       "Idaho                            0           0         0             4   \n",
       "Illinois                        14           6        13             4   \n",
       "Indiana                         13           0        10             4   \n",
       "Iowa                             6           6        14             4   \n",
       "Kansas                          10           0        13             4   \n",
       "Kentucky                         3          12        13             4   \n",
       "Louisiana                        4           0         8             4   \n",
       "Maryland                         3           0        14             4   \n",
       "Massachusetts                    0           0        14             4   \n",
       "Michigan                         2          14        14             4   \n",
       "Minnesota                       12           0        13             4   \n",
       "Mississippi                      0           0         0             3   \n",
       "Missouri                        10           5        14             4   \n",
       "Montana                          1           0         2             4   \n",
       "Nebraska                        11           0         4             4   \n",
       "Nevada                          12          11        14             4   \n",
       "New Hampshire                    0           0         0             3   \n",
       "New Jersey                       0           6        14             4   \n",
       "New Mexico                       0           8        10             4   \n",
       "New York                        10           3        14             4   \n",
       "North Carolina                  10           7        13             4   \n",
       "North Dakota                     0           0         0             4   \n",
       "Ohio                            12          13        14             4   \n",
       "Oklahoma                        14          14        12             4   \n",
       "Oregon                           0           0        14             4   \n",
       "Pennsylvania                    10           0        13             4   \n",
       "South Carolina                   3           4         3             4   \n",
       "South Dakota                     0           0         0             4   \n",
       "Tennessee                       14           0        14             4   \n",
       "Texas                           12          13        14             4   \n",
       "Utah                            12           0        14             4   \n",
       "Vermont                          0           0        14             0   \n",
       "Virginia                        14          13        14             4   \n",
       "Washington                      13          12        14             4   \n",
       "West Virginia                    3           1        14             4   \n",
       "Wisconsin                        7          13        13             4   \n",
       "Wyoming                          0           0         0             3   \n",
       "Max Quantity                    14          14        14             4   \n",
       "\n",
       "customer_type                              Max Quantity  \n",
       "product_category     Gift Basket Olive Oil               \n",
       "customer_state                                           \n",
       "Alabama                        4         4           14  \n",
       "Alaska                         2         4            8  \n",
       "Arizona                        4         4           12  \n",
       "Arkansas                       4         4           13  \n",
       "California                     4         4           14  \n",
       "Colorado                       4         4           10  \n",
       "Connecticut                    3         4           14  \n",
       "Delaware                       4         4            7  \n",
       "District of Columbia           4         4           14  \n",
       "Florida                        4         4           14  \n",
       "Georgia                        4         4           14  \n",
       "Hawaii                         3         4            4  \n",
       "Idaho                          4         4            4  \n",
       "Illinois                       4         4           14  \n",
       "Indiana                        4         4           13  \n",
       "Iowa                           4         4           14  \n",
       "Kansas                         4         4           13  \n",
       "Kentucky                       4         4           13  \n",
       "Louisiana                      4         4            8  \n",
       "Maryland                       4         4           14  \n",
       "Massachusetts                  4         4           14  \n",
       "Michigan                       4         4           14  \n",
       "Minnesota                      4         4           13  \n",
       "Mississippi                    4         4            4  \n",
       "Missouri                       4         4           14  \n",
       "Montana                        4         3            4  \n",
       "Nebraska                       4         4           11  \n",
       "Nevada                         4         4           14  \n",
       "New Hampshire                  4         3            4  \n",
       "New Jersey                     4         4           14  \n",
       "New Mexico                     4         4           10  \n",
       "New York                       4         4           14  \n",
       "North Carolina                 4         4           13  \n",
       "North Dakota                   2         4            4  \n",
       "Ohio                           4         4           14  \n",
       "Oklahoma                       4         4           14  \n",
       "Oregon                         4         4           14  \n",
       "Pennsylvania                   4         4           13  \n",
       "South Carolina                 4         4            4  \n",
       "South Dakota                   4         4            4  \n",
       "Tennessee                      4         4           14  \n",
       "Texas                          4         4           14  \n",
       "Utah                           4         4           14  \n",
       "Vermont                        0         0           14  \n",
       "Virginia                       4         4           14  \n",
       "Washington                     4         4           14  \n",
       "West Virginia                  1         4           14  \n",
       "Wisconsin                      4         4           13  \n",
       "Wyoming                        0         3            3  \n",
       "Max Quantity                   4         4           14  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sales_data.pivot_table(\n",
    "    values=\"quantity\",\n",
    "    index=\"customer_state\",\n",
    "    columns=[\"customer_type\", \"product_category\"],\n",
    "    aggfunc=\"max\",\n",
    "    fill_value=0,\n",
    "    margins=True,\n",
    "    margins_name=\"Max Quantity\",\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6b60d32b-465b-4c36-817a-f26d804238b3",
   "metadata": {},
   "source": [
    "In this solution, you have been asked to create a separate row for each `\"customer_state\"`, so you assign this to `index`. To create an analysis of `\"customer_type\"`, subdivided by `\"product_category\"`, you pass these as a list to `columns`. The main data you are basing your calculations is `\"quantity\"`, which becomes the `values` parameter, however, you must use the `\"max\"` function because you want the highest quantities to be shown. This is assigned to `aggfunc`. \n",
    "\n",
    "When you apply this plan to the various parameters, you get the code shown above. To solve the additional challenge, you assign `0` to the `fill_value` parameter. This replaces the `<NA>` values with `0`. You don't need to worry about rounding because here you are dealing with integers."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "15824a0c-5e63-4f15-bbb4-ac73dab97394",
   "metadata": {},
   "source": [
    "**Possible Solution: Using Sub-Rows**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "4a92c58d-40e3-4d72-9615-05ba96f7649e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>customer_type</th>\n",
       "      <th colspan=\"3\" halign=\"left\">Business</th>\n",
       "      <th colspan=\"3\" halign=\"left\">Individual</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>product_category</th>\n",
       "      <th>Bath products</th>\n",
       "      <th>Gift Basket</th>\n",
       "      <th>Olive Oil</th>\n",
       "      <th>Bath products</th>\n",
       "      <th>Gift Basket</th>\n",
       "      <th>Olive Oil</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>order_type</th>\n",
       "      <th>customer_state</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">Retail</th>\n",
       "      <th>Alabama</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>92</td>\n",
       "      <td>45</td>\n",
       "      <td>136</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Alaska</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>9</td>\n",
       "      <td>24</td>\n",
       "      <td>6</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Arizona</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>16</td>\n",
       "      <td>81</td>\n",
       "      <td>39</td>\n",
       "      <td>143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Arkansas</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>21</td>\n",
       "      <td>15</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>California</th>\n",
       "      <td>20</td>\n",
       "      <td>2</td>\n",
       "      <td>18</td>\n",
       "      <td>416</td>\n",
       "      <td>258</td>\n",
       "      <td>726</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">Wholesale</th>\n",
       "      <th>Vermont</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>33</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Virginia</th>\n",
       "      <td>27</td>\n",
       "      <td>15</td>\n",
       "      <td>280</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Washington</th>\n",
       "      <td>34</td>\n",
       "      <td>31</td>\n",
       "      <td>125</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>West Virginia</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>53</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wisconsin</th>\n",
       "      <td>7</td>\n",
       "      <td>15</td>\n",
       "      <td>97</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>88 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "customer_type                  Business                          Individual  \\\n",
       "product_category          Bath products Gift Basket Olive Oil Bath products   \n",
       "order_type customer_state                                                     \n",
       "Retail     Alabama                    2           3         4            92   \n",
       "           Alaska                     0           0         9            24   \n",
       "           Arizona                    0           0        16            81   \n",
       "           Arkansas                   0           0         0            21   \n",
       "           California                20           2        18           416   \n",
       "...                                 ...         ...       ...           ...   \n",
       "Wholesale  Vermont                    0           0        33             0   \n",
       "           Virginia                  27          15       280             0   \n",
       "           Washington                34          31       125             0   \n",
       "           West Virginia              0           0        53             0   \n",
       "           Wisconsin                  7          15        97             0   \n",
       "\n",
       "customer_type                                    \n",
       "product_category          Gift Basket Olive Oil  \n",
       "order_type customer_state                        \n",
       "Retail     Alabama                 45       136  \n",
       "           Alaska                   6        39  \n",
       "           Arizona                 39       143  \n",
       "           Arkansas                15        44  \n",
       "           California             258       726  \n",
       "...                               ...       ...  \n",
       "Wholesale  Vermont                  0         0  \n",
       "           Virginia                 0         0  \n",
       "           Washington               0         0  \n",
       "           West Virginia            0         0  \n",
       "           Wisconsin                0         0  \n",
       "\n",
       "[88 rows x 6 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sales_data.pivot_table(\n",
    "    values=\"quantity\",\n",
    "    index=[\"order_type\", \"customer_state\"],\n",
    "    columns=[\"customer_type\", \"product_category\"],\n",
    "    aggfunc=\"sum\",\n",
    "    fill_value=0,\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c11be931-897c-4679-9667-f9dc56535e49",
   "metadata": {},
   "source": [
    "In this solution, you have decided to create a separate row to analyze `\"order_type\"` by `\"customer_state\"`, so these are passed as a list into `index` in the order shown. To also analyze `\"customer_type\"` by `\"product_category\"`, these too are passed in as a list, but to the `columns` parameter. The main data you are basing your calculations on is `\"quantity\"`, so this becomes your `values` parameter, while `aggfunc=\"sum\"` ensures you see total quantities. \n",
    "\n",
    "When you apply this plan to the various parameters, you get the code shown above. To finish off, you once more assign `0` to the `fill_value` parameter to replace the `<NA>` values with `0`. You don't need to worry about formatting because you are dealing with integers."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c9a0c260-d5bf-462b-b7e3-b5a5e3dbcaa3",
   "metadata": {},
   "source": [
    "**Possible Solution: Calculating Multiple Values**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "32969680-7afb-4da7-a62a-453ade77a34a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">quantity</th>\n",
       "      <th colspan=\"3\" halign=\"left\">sale_price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>product_category</th>\n",
       "      <th>Bath products</th>\n",
       "      <th>Gift Basket</th>\n",
       "      <th>Olive Oil</th>\n",
       "      <th>Bath products</th>\n",
       "      <th>Gift Basket</th>\n",
       "      <th>Olive Oil</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>customer_type</th>\n",
       "      <th>order_type</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Business</th>\n",
       "      <th>Retail</th>\n",
       "      <td>113</td>\n",
       "      <td>61</td>\n",
       "      <td>201</td>\n",
       "      <td>$1_060.87</td>\n",
       "      <td>$3_678.50</td>\n",
       "      <td>$23_835.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wholesale</th>\n",
       "      <td>666</td>\n",
       "      <td>335</td>\n",
       "      <td>4506</td>\n",
       "      <td>$6_024.60</td>\n",
       "      <td>$18_787.50</td>\n",
       "      <td>$948_695.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Individual</th>\n",
       "      <th>Retail</th>\n",
       "      <td>3197</td>\n",
       "      <td>1829</td>\n",
       "      <td>5615</td>\n",
       "      <td>$32_711.58</td>\n",
       "      <td>$113_275.00</td>\n",
       "      <td>$560_633.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              quantity                          sale_price  \\\n",
       "product_category         Bath products Gift Basket Olive Oil Bath products   \n",
       "customer_type order_type                                                     \n",
       "Business      Retail               113          61       201     $1_060.87   \n",
       "              Wholesale            666         335      4506     $6_024.60   \n",
       "Individual    Retail              3197        1829      5615    $32_711.58   \n",
       "\n",
       "                                                  \n",
       "product_category         Gift Basket   Olive Oil  \n",
       "customer_type order_type                          \n",
       "Business      Retail       $3_678.50  $23_835.00  \n",
       "              Wholesale   $18_787.50 $948_695.75  \n",
       "Individual    Retail     $113_275.00 $560_633.00  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sales_data.pivot_table(\n",
    "    values=[\"sale_price\", \"quantity\"],\n",
    "    index=[\"customer_type\", \"order_type\"],\n",
    "    columns=\"product_category\",\n",
    "    aggfunc=\"sum\",\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "72a83264-e3d1-4f6f-b69e-73a2ba986a08",
   "metadata": {},
   "source": [
    "This time, you were asked to create a separate row analyzing `\"order_type\"` within `\"customer_type\"`, so these are passed as a list into `index` in the order shown. You also need the analysis to be by `\"product_category\"`, so you pass this as the `columns` parameter. The main data you are basing your calculations on is `\"quantity\"` and `\"sale_price\"`, so these form your `values` list, although their order must be reversed. The `aggfunc=\"sum\"` ensures you see totals. \n",
    "\n",
    "When you apply this plan to the various parameters, you get the code shown above. To finish off, you once more assign `0` to the `fill_value` parameter to replace the `<NA>` values with `0`. You don't need to worry about formatting `quantity`, because you are dealing with integers.\n",
    "\n",
    "If you need to apply proper currency formatting, you may need to add `pd.set_option(\"display.float_format\", \"${:_.2f}\".format)` before the pivot table is displayed. If this was set earlier, it still gets applied here. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bf0b203d-ca20-4135-9c69-35b3407ffa4e",
   "metadata": {},
   "source": [
    "**Possible Solution: More Advanced Aggregations**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "325d1f75-b672-460b-91c5-c5434f215427",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>product_number</th>\n",
       "      <th>sale_price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sales_region</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Central East</th>\n",
       "      <td>67</td>\n",
       "      <td>$251_751.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>N Central East</th>\n",
       "      <td>67</td>\n",
       "      <td>$269_898.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>N Central West</th>\n",
       "      <td>44</td>\n",
       "      <td>$11_737.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Northeast</th>\n",
       "      <td>67</td>\n",
       "      <td>$211_502.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Northwest</th>\n",
       "      <td>66</td>\n",
       "      <td>$67_805.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S Central East</th>\n",
       "      <td>67</td>\n",
       "      <td>$339_688.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S Central West</th>\n",
       "      <td>64</td>\n",
       "      <td>$105_732.83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Southeast</th>\n",
       "      <td>67</td>\n",
       "      <td>$223_864.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Southwest</th>\n",
       "      <td>67</td>\n",
       "      <td>$226_720.72</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               product_number  sale_price\n",
       "sales_region                             \n",
       "Central East               67 $251_751.40\n",
       "N Central East             67 $269_898.11\n",
       "N Central West             44  $11_737.92\n",
       "Northeast                  67 $211_502.31\n",
       "Northwest                  66  $67_805.74\n",
       "S Central East             67 $339_688.05\n",
       "S Central West             64 $105_732.83\n",
       "Southeast                  67 $223_864.72\n",
       "Southwest                  67 $226_720.72"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def count_unique(values):\n",
    "    return len(values.unique())\n",
    "\n",
    "\n",
    "sales_data.pivot_table(\n",
    "    values=[\"product_number\", \"sale_price\"],\n",
    "    index=\"sales_region\",\n",
    "    aggfunc={\"product_number\": count_unique, \"sale_price\": \"sum\"},\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3fb9deaf-fcbc-4514-8557-6da62112ba5b",
   "metadata": {},
   "source": [
    "You can reuse your `count_unique()` function to determine the unique records once more. This time you want each `sales_region` to be displayed in a row of its own, so this gets assigned to the `index` parameter. \n",
    "\n",
    "Your pivot table needs to calculate based on both `product_number` and `sale_price`, so these are assigned to `values` as a list. To apply the different calculations to each of the `values`, you assign the dictionary `{\"product_number\": count_unique, \"sale_price\": \"sum\"}` to `aggfunc`. Note that each dictionary key must also appear in `values`.\n",
    "\n",
    "This time your `count_unique()` function will work out the number of unique product numbers, in other words, unique products, in each `sales_region` while the `sum` function will work out the totals."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
